# 导入通用 did
# 2022-03-09
# yeyi

import openpyxl
import pymysql
import time
import traceback

# 输出文件名
fileName = 'common-did'
fileNameTimeStr = time.strftime('%Y-%m-%d_%H-%M-%S',time.localtime(time.time()))
logFileName = fileName + '_' + fileNameTimeStr

def printt(s):
    f = open(logFileName+'.log', 'a+', encoding='utf-8')
    print(str(s),file=f)
    f.close()
    print(s)

# 统一处理异常
def dealException(e):
    f = open(logFileName+'.log', 'a+', encoding='utf-8')
    traceback.print_exc(file=f)
    print(str(e),file=f)
    f.close()
    print(str(e))

# 建立mysql链接
db = pymysql.connect(host='10.2.3.5',  # 10.28.10.2   ds开发环境
                     port=3306,
                     user='root',  # dev dskj
                     passwd='sirun@2018',  # dev Dskj!2019
                     db='bss-gq',
                     charset='utf8')
# 建立mysql链接
# db = pymysql.connect(host='gz-cdb-o43ukrr9.sql.tencentcdb.com',
#                        port=60652,
#                        user='dskj',
#                        passwd='Dskj!2019',
#                        db = 'bss-gq',
#                        charset='utf8')
cursor = db.cursor()  # 使用cursor()方法获取操作游标
# 打开excel文件,获取工作簿对象
wdid = openpyxl.load_workbook("能力集脚本\excel\公用DID.xlsx")

# 目标 ecuId
ecuIds = []
# 5858000001 ~ 5858000024
for i in range(0,24):
    num=5858000001+i
    ecuIds.append(str(num))
number = 1
didnum = 1
item = {}
for ecuId in ecuIds:
    # ECU id是数据库中的主键id，这里需要用到ecu_id利用ecu_code生成的规则
    item['ecu_id'] = ecuId
    number = number + 1
    printt('对应ecu_id是' + str(ecuId))
    for k in range(2, 12):
        # 获取公共did表单，对于每一个ecu都有公共的did
        wsdid = wdid.active
        item['did_id'] = str(ecuId) + "%03d" % didnum
        item['did_code'] = int(wsdid.cell(row=k, column=1).value,16)  # did编码
        item['did_name'] = wsdid.cell(row=k, column=3).value  # did英文name
        item['did_name_cn'] = wsdid.cell(row=k, column=4).value  # did中文name
        item['did_len'] = wsdid.cell(row=k, column=7).value  # did长度
        item['sub_data_name'] = wsdid.cell(row=k, column=5).value  # 子数据英文名
        item['sub_data_name_cn'] = wsdid.cell(row=k, column=6).value  # 子数据中文名
        item['data_type'] = wsdid.cell(row=k, column=15).value  # 数据类型
        item['app_support'] = wsdid.cell(row=k, column=16).value  # app支持权限
        item['did_byte'] = wsdid.cell(row=k, column=8).value  # did字节
        item['did_bit'] = wsdid.cell(row=k, column=9).value  # did比特位
        didnum = didnum + 1
        printt(item)
        into = "insert into `tab_did` (`did_id`, `ecu_id`, `did_code`, `did_name`, `did_name_cn`,`did_len`," \
               "`sub_data_name`,`sub_data_name_cn`,`data_type`,`app_support`,`did_byte`,`did_bit`) " \
               "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        values = (item['did_id'], item['ecu_id'], item['did_code'], item['did_name'], item['did_name_cn'],
                  item['did_len'], item['sub_data_name'], item['sub_data_name_cn'], item['data_type'],
                  item['app_support'], item['did_byte'], item['did_bit'])

        try:
            cursor.execute(into, values)
            db.commit()
        except Exception as e:
            printt('PEPS err ecuId: '+ecuId+' i: '+str(item['did_code']))
            dealException(e)
